package org.hgvc.webapp.ets.spring.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.bo.AgreementControl;
import org.hgvc.webapp.ets.bo.Calendar;
import org.hgvc.webapp.ets.bo.DocCheckList;
import org.hgvc.webapp.ets.bo.DocSubmission;
import org.hgvc.webapp.ets.bo.Finance;
import org.hgvc.webapp.ets.bo.InstallmentSchema;
import org.hgvc.webapp.ets.bo.Inventory;
import org.hgvc.webapp.ets.bo.Invoice;
import org.hgvc.webapp.ets.bo.InvoiceDtl;
import org.hgvc.webapp.ets.bo.Member;
import org.hgvc.webapp.ets.bo.MemberComp;
import org.hgvc.webapp.ets.bo.MemberInd;
import org.hgvc.webapp.ets.bo.MemberIndAcct;
import org.hgvc.webapp.ets.bo.MemberInstallmentPlan;
import org.hgvc.webapp.ets.bo.MemberPaymentPlan;
import org.hgvc.webapp.ets.bo.MemberPointEntitle;
import org.hgvc.webapp.ets.bo.MemberTransaction;
import org.hgvc.webapp.ets.bo.MemberTransactionEntitlement;
import org.hgvc.webapp.ets.bo.MicTask;
import org.hgvc.webapp.ets.bo.PackageProducts;
import org.hgvc.webapp.ets.bo.PointMembershipMap;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hgvc.webapp.ets.constant.MemberConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;
import org.jocsb.base.bo.UserRole;
import org.jocsb.base.util.DateUtil;


public class MemberHbmDao extends AbstractHibernateDao {

	public List<MemberPointEntitle> getLatestEntitlement(){
		StringBuffer str = new StringBuffer();
		str.append(" select max(m.year) from  MemberEntitlement m " +
				" ");

		Query query = getSession().createQuery(str.toString()); 
	 
		return (List<MemberPointEntitle>)query.list();
	}
	
	public void saveObjects(List objs) throws Exception
	{
	    for(Iterator it = objs.iterator(); it.hasNext(); getHibernateTemplate().save(it.next()));
	}
	
	public void updateObjects(List objs) throws Exception
	{
	    for(Iterator it = objs.iterator(); it.hasNext(); getHibernateTemplate().update(it.next()));
	}
	
	public List<String> getMonthYearOfAgreementMembershipChange(String agentId, int month, int year){
		StringBuffer str = new StringBuffer();
		str.append(" select distinct(month(m.agreement.agreetmentDate) ||';' ||year(m.agreement.agreetmentDate) ) from MembershipChangeLog m " +
				" where month(m.approvedDate)=:month " +
				"	and year(m.approvedDate)=:year " +
				"	and m.agreement.member.agent.agentId =:agentId");

		Query query = getSession().createQuery(str.toString()); 
		query.setParameter("month", month); 
		query.setParameter("year", year); 
		query.setParameter("agentId", agentId);
		return (List<String>)query.list();
	}
	
	public List<Member> getMemberList() throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.isDeleted=:value");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Member>)query.list();
	}

	public List<MemberInd> getMemberInvList(String membershipNo,String memberName,String icNo)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select memberInd from MemberInd memberInd ");
		str.append(" where 1=1 ");

		if (membershipNo!=null&&membershipNo.length()>0)
			str.append(" and memberInd.member.membershipNo=:membershipNo ");
		if (memberName!=null&&memberName.length()>0)
			str.append(" and memberInd.memberName like=:memberName ");
		if (icNo!=null&&icNo.length()>0)
			str.append(" and memberInd.newIcNo like=:icNo ");
		str.append(" and memberInd.member.isDeleted=:value ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		if (membershipNo!=null&&membershipNo.length()>0)
			query.setParameter("membershipNo", membershipNo);
		if (memberName!=null&&memberName.length()>0)
			query.setParameter("memberName", memberName);
		if (icNo!=null&&icNo.length()>0)
			query.setParameter("icNo", icNo);

		return (List<MemberInd>)query.list();
	}

	public MemberIndAcct getMemberIndAcctByMemberId(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select memberIndAcct from MemberIndAcct memberIndAcct where memberIndAcct.memberInd.member.memberId=:value");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", memberId);
		return (MemberIndAcct)query.uniqueResult();
	}

	public MemberComp getMemberCompByMemberId(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select memberComp from MemberComp memberComp where memberComp.member.memberId=:value");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", memberId);
		return (MemberComp)query.uniqueResult();
	}

	public Finance getFinanceByProspectId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select fin from  Finance fin where fin.prospectInfo.prospectInfoId=:id and fin.isDeleted=:value ");

		Query query = getSession().createQuery(str.toString());

		query.setParameter("id", id);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (Finance)query.uniqueResult();
	}

	public Finance getFinanceByMembertId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select fin from  Finance fin where fin.agreement.member.memberId=:id and fin.isDeleted=:value ");

		Query query = getSession().createQuery(str.toString());

		query.setParameter("id", id);
		query.setParameter("value", CommonConstant.NOT_DELETED);

		return (Finance)query.uniqueResult();
	}

	public List<InvoiceDtl> getDepositInvoiceDtl(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select invoiceDtl from  InvoiceDtl invoiceDtl,Invoice inv,Finance fin  ");
		str.append(" where fin.member.memberId=:memberId and inv.finance.financeId=fin.financeId  ");
		str.append(" and invoiceDtl.invoice.invoiceId = inv.invoiceId ");
		str.append(" and invoiceDtl.itemType=:type ");

		Query query = getSession().createQuery(str.toString());

		query.setParameter("memberId", memberId);
		query.setParameter("type",CommonConstant.INVOICE_TYPE_DEPOSIT);

		return (List<InvoiceDtl>)query.list();
	}

	public MemberPaymentPlan getMemberPaymentPlanByMemberId(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select plan from  MemberPaymentPlan plan where plan.member.memberId=:memberId");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);

		return (MemberPaymentPlan)query.uniqueResult();
	}

	public List<MemberInstallmentPlan> getMemberInstallmentPlanByPlanId(String planId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select plan from  MemberInstallmentPlan plan where plan.memberPaymentPlan.memberPaymentPlanId=:planId");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("planId", planId);

		return (List<MemberInstallmentPlan>)query.list();
	}

	public Agreement getAgreementByProspectId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select a from Agreement a where a.prospectInfo.prospectInfoId=:id");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);

		return (Agreement)query.uniqueResult();
	}

	public List<MemberInstallmentPlan> getMemberInstallmentPlanListByMemberId(String memberId)throws Exception{
		Criteria memberInstallPlanCrt = getSession().createCriteria(MemberInstallmentPlan.class);
		Criteria memberPaymentPlanCrt = memberInstallPlanCrt.createCriteria("memberPaymentPlan");
		Criteria memberCrt = memberPaymentPlanCrt.createCriteria("member");
		memberCrt.add(Restrictions.eq("memberId", memberId));
		memberInstallPlanCrt.add(Restrictions.eq("isDeleted", CommonConstant.NOT_DELETED));

		List<MemberInstallmentPlan> list=memberInstallPlanCrt.list();
		return list;
	}
	
	public List<MemberInd> getMemberIndByIcNo(String icNo)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select m from MemberInd m where m.newIcNo=:icNo and m.isDeleted=:value");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("icNo", icNo);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return query.list();
	}
	
	public List<Calendar> getCalendarList(Date start,Date end)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select c from Calendar c where c.isDeleted=:value");
		str.append(" and c.date>=:start and c.date <=:end");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("start", start);
		query.setParameter("end", end);
		return query.list();
	}
	
	public List<Calendar> getCalendarList()throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select c from Calendar c where c.isDeleted=:value");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return query.list();
	}
	
	public List<Agreement> getAgreementListByAgreementNo(String agreementNo)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select a from Agreement a where a.isDeleted=:value");
		str.append(" and a.agreetmentNo=:agreementNo");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agreementNo", agreementNo);
		return query.list();
	}
	
	public void deleteMemberRoleByLoginUserId(String userId,String roleId)throws Exception{
		Criteria criteria = getSession().createCriteria(UserRole.class);
		criteria.add(Restrictions.eq("role.roleId", roleId));
		criteria.add(Restrictions.eq("user.userId", userId));
		
		List<UserRole> list=criteria.list();
		
		deleteAll(list);
		
		/*StringBuffer str = new StringBuffer();
		str.append(" delete from UserRole usrRole where usrRole.role.roleId=:roleId ");
		str.append(" and usrRole.user.userId=:userId ");
		
		Query query = getSession().createQuery(str.toString());
		query.executeUpdate();*/
	}
	
	public List<Member> getMemberListByStatus(String status) throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.status=:status and me.isDeleted=:value");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("status", status);
		return (List<Member>)query.list();
	}
	
	public Member getMemberByStatus(String status,String memberId) throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.status=:status and me.isDeleted=:value and me.memberId=:memberId");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("status", status);
		query.setParameter("memberId", memberId);
		return (Member)query.uniqueResult();
	}
	
	public List<MemberComp> getMemberCompByCompName(String compName)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select me from MemberComp me where me.compName=:compName and me.isDeleted=:value");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("compName", compName);
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return query.list();
	}
	
	public Member getMemberByUserId(String userId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select me from Member me where me.loginUser.userId=:userId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("userId", userId);
		
		return (Member)query.uniqueResult();
	}
	
	public MemberInd getMemberIndByMemberId(String memberId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select me from MemberInd me where me.member.memberId=:memberId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("memberId", memberId);
		
		return (MemberInd)query.uniqueResult();
	}
	
	
	public List<MemberIndAcct> getMemberIndAcctListByMemberCompId(String memberId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select me from MemberIndAcct me where me.memberComp.memberCompId=:memberId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("memberId", memberId);
		return (List<MemberIndAcct>)query.list();
	}
	
	public List<MemberIndAcct> getMemberIndAcctListByMemberIndId(String memberId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select me from MemberIndAcct me where me.memberInd.memberIndId=:memberId");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("memberId", memberId);
		return (List<MemberIndAcct>)query.list();
	}
	
	public List<DocCheckList> getDocCheckList()throws Exception{

		StringBuffer str = new StringBuffer();
		str.append(" select check from DocCheckList check where  check.isDeleted=:value");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return query.list();
	}
	
	
	public List<DocCheckList> getDocCheckListCodeTitleType(String code,String title,String type)throws Exception{

		StringBuffer str = new StringBuffer();
		str.append(" select check from DocCheckList check where  check.isDeleted=:value");
		str.append(" and check.type=:type and (check.code=:code or check.title=:title)");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("code",code);
		query.setParameter("title", title);
		query.setParameter("type", type);
		return query.list();
	}
	
	public List<DocCheckList> getDocCheckListType(String type)throws Exception{

		StringBuffer str = new StringBuffer();
		str.append(" select check from DocCheckList check where  check.isDeleted=:value");
		str.append(" and check.type=:type ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("type", type);
		return query.list();
	}
	
	public List<Member> getMemberListWithStatus(List<String> statusList)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.isDeleted=:value  ");
		if(statusList.size()>0){
			str.append(" and (");
		}
		int i = 0;
		for(String status:statusList){
			if(i>0){
				str.append(" or ");
			}
			str.append(" me.status= '"+status+"'");
			i++;
			
		}
		if(statusList.size()>0){
			str.append(" )");
		}
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		
		return (List<Member>)query.list();
		
	}
	
	public List<DocSubmission> getDocSubmission(String memberId,String type)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select doc from DocSubmission doc where doc.docCheckList.isRequired=:type  ");
		str.append(" and doc.member.memberId=:memberId  ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		query.setParameter("type", type);
		
		return (List<DocSubmission>)query.list();
	}
	
	public List<Member> getMemberListByMembershipNo(String membershipNo)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.membershipNo=:membershipNo and me.isDeleted=:value  ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("membershipNo", membershipNo);
		
		return (List<Member>)query.list();
		
	}
 
	public Agreement getAgreementByMemberId(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select agreement from Agreement agreement where agreement.member.memberId=:memberId ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		return (Agreement)query.uniqueResult();
	}
	
	public List<Member> getAgentMemberListWithStatus(List<String> statusList,String agentId)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.isDeleted=:value and me.agent.agentId=:agentId ");
		if(statusList.size()>0){
			str.append(" and (");
		}
		int i = 0;
		for(String status:statusList){
			if(i>0){
				str.append(" or ");
			}
			str.append(" me.status= '"+status+"'");
			i++;
			
		}
		if(statusList.size()>0){
			str.append(" )");
		}
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		
		return (List<Member>)query.list();
		
	}
	
	public List<Inventory> getInventory(Date date, String partnerProductId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select i from Inventory i where i.resortDate = :resortDate and i.partnerProduct.partnerProductId=:partnerProductId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("resortDate", date);
		query.setParameter("partnerProductId", partnerProductId);
		
		return (List<Inventory>)query.list();
	}
	
	public List<PackageProducts> getPackageProducts(String packageId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select p from PackageProducts p where p.packages.packageId=:packageId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("packageId", packageId);
		
		return (List<PackageProducts>)query.list();
	}
	
	public List<MicTask> getMicTask(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select m from MicTask m where m.member.memberId=:memberId order by m.date");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		return (List<MicTask>)query.list();
	}
	
	public List<InstallmentSchema> getInstallmentSchema()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append("select schema from InstallmentSchema schema where schema.isDeleted=:value order by schema.month");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<InstallmentSchema>)query.list();
	}
	
	public List<AgreementControl> getAgreementControlByAgentId(String agentId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append(" select control from AgreementControl control where control.isDeleted=:value ");
		strb.append(" and control.agent.agentId=:agentId ");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId", agentId);
		
		return (List<AgreementControl>)query.list();
	}
	
	public List<PointMembershipMap> getPointMembershipMapList(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select p from PointMembershipMap p where p.isDeleted=:value ");
		str.append(" and p.membership.membershipId=:id ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("id", id);
		return (List<PointMembershipMap>)query.list();
	}
	//-----Added By Jacky 26-Nov-2008 1925-----
	public List<MemberPointEntitle> getMemberPointEntitleActiveList(String agreementId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select mp from MemberPointEntitle mp where mp.isDeleted=:value ");
		str.append(" and mp.agreement.agreementId=:agreementId ");
		str.append(" and mp.expiryDate>=:todayDate ");
		str.append(" Order by mp.expiryDate asc ");
		
		Date todayDate = DateUtil.formatDateByTime(new Date(), 0, 0, 0, 0);
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agreementId", agreementId);
		query.setParameter("todayDate", todayDate);
		
		return (List<MemberPointEntitle>)query.list();
	}
	//-------------------------------------------
	
	// added by jimmy for booking
	public Agreement getAgreementByAgreeNo(String agreementNo)throws Exception{
		Criteria criteria=getSession().createCriteria(Agreement.class);
		criteria.add(Restrictions.eq("agreetmentNo", agreementNo));
		List<Agreement> list=criteria.list();
		if (list!=null && list.size()!=0){
			return (Agreement)list.get(0);
		}
		return null;
	}
	
	public List<MemberPointEntitle> getValidMemberPointEntitleList(String memberId)throws Exception{
 		StringBuffer str = new StringBuffer();
		str.append(" select mp from MemberPointEntitle mp where mp.isDeleted=:value ");
		str.append(" and mp.agreement.member.memberId=:memberId " +
				"	and mp.status=:status " +
				" and mp.expiryDate>=:toDayDate " +
				" order by mp.agreement.agreetmentNo, mp.year ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("memberId", memberId);
		query.setParameter("status", MemberConstant.MEMBER_POINT_VALID);
		query.setParameter("toDayDate", new Date());
		
		return (List<MemberPointEntitle>)query.list();
	}
	public List<MemberPointEntitle> getValidMemberPointEntitleList(String agreetmentNo, int year)throws Exception{
 		StringBuffer str = new StringBuffer();
		str.append(" select mp from MemberPointEntitle mp where mp.isDeleted=:value ");
		str.append(" and mp.year=:year " +
				"	and mp.status=:status and mp.agreement.agreetmentNo=:agreetmentNo "+
				" and mp.expiryDate>=:toDayDate " +
				" order by mp.agreement.agreetmentNo ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agreetmentNo", agreetmentNo);
		query.setParameter("year", year);
		query.setParameter("status", MemberConstant.MEMBER_POINT_VALID);
		query.setParameter("toDayDate", new Date());
		
		return (List<MemberPointEntitle>)query.list();
	}
	public List<Member> getMemberListWithStatus(List<String> statusList,org.hgvc.webapp.ets.bo.Agent agent)throws Exception{
		
		StringBuffer str = new StringBuffer();
		str.append(" select me from Member me where me.isDeleted=:value and me.agent.agentId=:agentId ");
		if(statusList.size()>0){
			str.append(" and (");
		}
		int i = 0;
		for(String status:statusList){
			if(i>0){
				str.append(" or ");
			}
			str.append(" me.status= '"+status+"'");
			i++;
			
		}
		if(statusList.size()>0){
			str.append(" )");
		}
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agentId",agent.getAgentId() );
		
		return (List<Member>)query.list();
		
	}
	//////////////////end jimmy///////////////////////
	
	public List<MemberPointEntitle> getMemberPointEntitleList(String agreementId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select mp from MemberPointEntitle mp where mp.isDeleted=:value ");
		str.append(" and mp.agreement.agreementId=:agreementId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agreementId", agreementId);
		return (List<MemberPointEntitle>)query.list();
	}
	
	public List<MemberPointEntitle> getMemberPointEntitleList(String agreementId,String pointCatId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select mp from MemberPointEntitle mp where mp.isDeleted=:value ");
		str.append(" and mp.agreement.agreementId=:agreementId ");
		str.append(" and mp.pointCategory.pointCatId=:pointCatId ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("agreementId", agreementId);
		query.setParameter("pointCatId", pointCatId);
		
		return (List<MemberPointEntitle>)query.list();
	}
	
	public List<Agreement> getAgreementListByMemberId(String memberId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select agreement from Agreement agreement where agreement.member.memberId=:memberId ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		return (List<Agreement>)query.list();
	}
	
	public List<Agreement> getActiveAgreementListByAgent(String agentId)throws Exception{
		StringBuffer str = new StringBuffer(); 
		str.append(" select agreement from Agreement agreement where " +
				" agreement.member.agent.agentId=:agentId ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("agentId", agentId);
		 
		List<Agreement> agreementMayExpired = (List<Agreement>)query.list();
		List<Agreement> agreements = new ArrayList<Agreement>();
		for (Agreement agreement2 : agreementMayExpired) {
			if (agreement2.getAgreetmentDate()!=null){
				java.util.Calendar cal = DateUtil.setTime(agreement2.getAgreetmentDate());
				cal.add(java.util.Calendar.YEAR, agreement2.getMembership().getMembershipTerm());
				Date expiredDate = DateUtil.formatDateByTime(cal.getTime(), 23, 59, 59, 999);
				if(agreement2.getMember().getMemberType().equalsIgnoreCase(CommonConstant.MEMBER_TYPE_COMP)){
					MemberComp memberComp = getMemberCompByMemberId(  agreement2.getMember().getMemberId());
					agreement2.getMember().setName(memberComp.getCompName());
				}else{
					MemberInd memberInd = getMemberIndByMemberId( agreement2.getMember().getMemberId());
					agreement2.getMember().setName(memberInd.getNameAsIcPassport());
				}
				if (expiredDate.after(new Date())) {
					agreements.add(agreement2);
				}
			}
		}
		
		return agreements ;
	}
	public List<Agreement> getActiveAgreementListByMember(String memberId)throws Exception{
		StringBuffer str = new StringBuffer(); 
		str.append(" select agreement from Agreement agreement where " +
				" agreement.member.memberId=:memberId ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("memberId", memberId);
		 
		List<Agreement> agreementMayExpired = (List<Agreement>)query.list();
		List<Agreement> agreements = new ArrayList<Agreement>();
		for (Agreement agreement2 : agreementMayExpired) {
			if (agreement2.getAgreetmentDate()!=null){
				java.util.Calendar cal = DateUtil.setTime(agreement2.getAgreetmentDate());
				cal.add(java.util.Calendar.YEAR, agreement2.getMembership().getMembershipTerm());
				Date expiredDate = DateUtil.formatDateByTime(cal.getTime(), 23, 59, 59, 999);
				if(agreement2.getMember().getMemberType().equalsIgnoreCase(CommonConstant.MEMBER_TYPE_COMP)){
					MemberComp memberComp = getMemberCompByMemberId(  agreement2.getMember().getMemberId());
					agreement2.getMember().setName(memberComp.getCompName());
				}else{
					MemberInd memberInd = getMemberIndByMemberId( agreement2.getMember().getMemberId());
					agreement2.getMember().setName(memberInd.getNameAsIcPassport());
				}
				if (expiredDate.after(new Date())) {
					agreements.add(agreement2);
				}
			}
		}
		
		return agreements ;
	}
	public List<InvoiceDtl> getInvoiceDtlListByAgreementId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select dtl from InvoiceDtl dtl where dtl.invoice.refId=:id ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (List<InvoiceDtl>)query.list();
		
	}
	
	public List<InvoiceDtl> getInvoiceDtlListByAgreementId(List<String> idList)throws Exception{		
		Criteria criteria = getSession().createCriteria(InvoiceDtl.class);
		criteria.createAlias("invoice", "invoice");
		criteria.add(Restrictions.in("invoice.refId", idList));
		return (List<InvoiceDtl>)criteria.list();
	}
	
	public List<Invoice> getInvoiceByAgreementId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select invoice from Invoice invoice where invoice.refId=:id ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (List<Invoice>)query.list();
		
	}
	
	public InvoiceDtl getInvoiceDtlByInvoiceId(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select dtl from InvoiceDtl dtl where dtl.invoice.invoiceId=:id ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("id", id);
		
		return (InvoiceDtl)query.uniqueResult();
	}
	
	public List<Agreement> getAgreementListByMemberSearch(String memberSearch) throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select ag from Agreement ag where ag.member.isDeleted=:value1 and ag.isDeleted=:value2");
		if(memberSearch!=null & memberSearch.trim().length()>0)
			str.append(" and ag.member.membershipNo like :memberSearch ");

		Query query = getSession().createQuery(str.toString());
		query.setParameter("value1", CommonConstant.NOT_DELETED);
		query.setParameter("value2", CommonConstant.NOT_DELETED);
		if(memberSearch!=null & memberSearch.trim().length()>0)
			query.setParameter("memberSearch", "%" + memberSearch + "%");
		return (List<Agreement>)query.list();
	}
	
	public List<Member> getPropectPendingMemberList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select m from Member m,Agreement a ");
		str.append(" where m.memberId=a.member.memberId ");
		str.append(" and m.status=:status ");
		str.append(" and a.endCoolingPeriod<=:endCoolingPeriod ");
		
		Date todayDate = DateUtil.formatDateByTime(new Date(), 23, 59, 59, 59);
		
		String endTodayDate = DateUtil.dateToString(new Date(), "yyyy-MM-dd");
		endTodayDate = endTodayDate.concat(" 23:59:59");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("status", MemberConstant.MEMBER_STATUS_PENDING_MEMBER);
		query.setParameter("endCoolingPeriod", todayDate);
		
		List<Member> list = query.list();
		return list;
	}
	
	
	public Integer getTotalMemberEntitlementPoints(String agreementId)throws Exception{
		StringBuffer strb = new StringBuffer();
		
		strb.append(" select sum(mpe.point) from MemberPointEntitle mpe ");
		strb.append(" where mpe.isDeleted=:isDeleted and mpe.agreement.agreementId=:agreementId ");
		strb.append(" and mpe.expiryDate>=:todayDate ");
		
		Date todayDate = DateUtil.formatDateByTime(new Date(), 0, 0, 0, 0);
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		query.setParameter("agreementId", agreementId);
		query.setParameter("todayDate", todayDate);
		
		List<Integer> list = query.list();
		
		Integer totalPoint = null;
		
		if (list.size()>0)
			totalPoint = Integer.parseInt(String.valueOf( list.get(0)));
		return totalPoint;
	}
	
	public Integer getTotalMemberReservedPoints(String agreementId)throws Exception{
		StringBuffer strb = new StringBuffer();
		
		strb.append(" select sum(mpe.reservedPoint) from MemberPointEntitle mpe ");
		strb.append(" where mpe.isDeleted=:isDeleted and mpe.agreement.agreementId=:agreementId ");
		strb.append(" and mpe.expiryDate>=:todayDate ");
		
		Date todayDate = DateUtil.formatDateByTime(new Date(), 0, 0, 0, 0);
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		query.setParameter("agreementId", agreementId);
		query.setParameter("todayDate", todayDate);
		
		List<Integer> list = query.list();
		
		Integer totalPoint = null;
		
		if (list.size()>0)
			totalPoint = Integer.parseInt(String.valueOf(list.get(0))) ;
		return totalPoint;
	}
	
	public List<MemberTransaction> getMemberTransactionList(String agreementId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append("select m from MemberTransaction m ");
		strb.append("where m.agreement.agreementId=:agreementId and m.isDeleted=:isDeleted ");
		strb.append("order by m.transactionDate desc ");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("agreementId", agreementId);
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		
		List<MemberTransaction> list = query.list();
		return list;
	}
	
	public List<MemberTransactionEntitlement> getMemberTransactionEntitlementList(String memberTransactionId)throws Exception{
		StringBuffer strb = new StringBuffer();
		strb.append("select m from MemberTransactionEntitlement m ");
		strb.append("where m.memberTransaction.memberTransactionId=:memberTransactionId and m.isDeleted=:isDeleted ");
		strb.append("order by m.expiryDate desc ");
		
		Query query = getSession().createQuery(strb.toString());
		query.setParameter("memberTransactionId", memberTransactionId);
		query.setParameter("isDeleted", CommonConstant.NOT_DELETED);
		
		List<MemberTransactionEntitlement> list = query.list();
		return list;
	}
}
